﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Raise.Monitor.Model;
using Raise.Monitor.Tools;
using Dapper;
using Newtonsoft.Json;

namespace Raise.Monitor.Services {
    public class OracleStrategy : Strategy {
        /// <summary>
        /// 查询字典项
        /// </summary>
        /// <param name="name">字典项名称</param>
        /// <returns>KeyValueItem集合</returns>
        public override List<KeyValueItem> GetKeyValueItems(string name) {
            try {
                if(CacheBuffer.Get(GlobalVar.KEY_VALUE_ITEM) == null) {
                    var result = Connection.Query<KeyValueItem>("Select * from KeyValueItem k where k.Status = 2");
                    if(result.Any())
                        CacheBuffer.Set(GlobalVar.KEY_VALUE_ITEM, JsonConvert.SerializeObject(result));
                }
            } catch(Exception e) {
                Logger.Error(e);
            }

            if(CacheBuffer.Get(GlobalVar.KEY_VALUE_ITEM) == null)
                return Activator.CreateInstance<List<KeyValueItem>>();
            var keyValueItemsAll = JsonConvert.DeserializeObject<List<KeyValueItem>>(CacheBuffer.Get(GlobalVar.KEY_VALUE_ITEM).ToString());
            var keyValueItems = keyValueItemsAll.Where(data => data.Name == name).ToList();
            keyValueItems.Add(new KeyValueItem {
                Key = -1,
                Value = "不限"
            });
            return keyValueItems.OrderBy(data => data.Key).ToList();
        }

        public override PageData<MonitorLogRecordConfigView> GetConfigRulesWithLogs(int pageIndex, string keyWords, string method, string serviceName, int? status, DateTime beginTime, DateTime endTime) {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("select m.responseMessage,m.ruleconfigid,m.operationtype,m.message,m.createtime,m.executetime,m.statusCode,m.statusCodeDescription,r.id,r.cron,r.description,r.triggername,r.jobname,r.method,r.postbody,r.servicename,r.author,r.contenttype,r.isauthentication,r.username,r.password,r.groupname,r.address,r.status,r.iswebservice,r.uniquecode,r.runstatus,r.businessname,rownum as rowno from monitorlogrecord m inner join ruleconfig r on m.ruleconfigid = r.id where 1=1 ");
            if(!string.IsNullOrEmpty(keyWords)) {
                sqlBuilder.AppendFormat("and (Upper(r.Description) like Upper('%{0}%') or Upper(r.Method) like Upper('%{0}%') or Upper(r.PostBody) like Upper('%{0}%') or Upper(r.ContentType) like Upper('%{0}%') or Upper(userName) like Upper('%{0}%')) ", keyWords.ToUpper()).AppendLine();
            }
            if(!string.IsNullOrEmpty(method)) {
                sqlBuilder.AppendFormat("and upper(r.Method) like Upper('%{0}%') ", method.ToUpper()).AppendLine();
            }
            if(status.HasValue && status > 0) {
                sqlBuilder.AppendFormat("and r.Status = {0} ", status.Value).AppendLine();
            }
            if(!string.IsNullOrEmpty(serviceName)) {
                sqlBuilder.AppendFormat("and upper(r.ServiceName) like Upper('%{0}') ", serviceName.ToUpper()).AppendLine();
            }

            sqlBuilder.AppendFormat("and m.CreateTime >= to_date('{0:yyyy-MM-dd}','yyyy-mm-dd') ", beginTime).AppendLine();
            sqlBuilder.AppendFormat("and m.CreateTime <= to_date('{0:yyyy-MM-dd}','yyyy-mm-dd') ", endTime.AddDays(1)).AppendLine();

            return this.ExecuteWithPaging<MonitorLogRecordConfigView>(sqlBuilder.ToString(), pageIndex);
        }

        public override PageData<SystemLog> GetSystemLogs(int pageIndex, string keyWords, string callsite, string logLevel, DateTime beginTime, DateTime endTime) {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("select r.*,rownum as rowno from SysLog r where 1=1 ");
            if(!string.IsNullOrEmpty(keyWords)) {
                sqlBuilder.AppendFormat("and (Upper(r.Message) like Upper('%{0}%') or Upper(r.callsite) like Upper('%{0}%')) ", keyWords.ToUpper()).AppendLine();
            }
            if(!string.IsNullOrEmpty(callsite)) {
                sqlBuilder.AppendFormat("and upper(r.callsite) like Upper('%{0}') ", callsite).AppendLine();
            }
            if(!string.IsNullOrEmpty(logLevel)) {
                sqlBuilder.AppendFormat("and r.LogLevel = '{0}' ", logLevel).AppendLine();
            }

            sqlBuilder.AppendFormat("and r.CreateTime >= to_date('{0:yyyy-MM-dd}','yyyy-mm-dd') ", beginTime).AppendLine();
            sqlBuilder.AppendFormat("and r.CreateTime <= to_date('{0:yyyy-MM-dd}','yyyy-mm-dd') ", endTime.AddDays(1)).AppendLine();

            return this.ExecuteWithPaging<SystemLog>(sqlBuilder.ToString(), pageIndex);
        }

        public override PageData<RuleConfig> GetConfigRules(int pageIndex, string keyWords, string requestType, string serviceName, int? runStatus, int? status) {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("select r.*,rownum as rowno from ruleconfig r where 1=1 ");
            if(!string.IsNullOrEmpty(keyWords)) {
                sqlBuilder.AppendFormat("and (Upper(r.Description) like Upper('%{0}%') or Upper(r.Method) like Upper('%{0}%') or Upper(r.PostBody) like Upper('%{0}%') or Upper(r.ContentType) like Upper('%{0}%') or Upper(userName) like Upper('%{0}%')) ", keyWords.ToUpper()).AppendLine();
            }
            if(!string.IsNullOrEmpty(requestType)) {
                sqlBuilder.AppendFormat("and upper(r.method) like Upper('%{0}%') ", requestType.ToUpper()).AppendLine();
            }
            if(runStatus.HasValue && runStatus > 0) {
                sqlBuilder.AppendFormat("and r.RunStatus = {0} ", runStatus.Value).AppendLine();
            }
            if(status.HasValue && status > 0) {
                sqlBuilder.AppendFormat("and r.status = {0} ", status.Value).AppendLine();
            }
            if(!string.IsNullOrEmpty(serviceName)) {
                sqlBuilder.AppendFormat("and Upper(r.ServiceName) like Upper('%{0}') ", serviceName.ToUpper()).AppendLine();
            }

            return this.ExecuteWithPaging<RuleConfig>(sqlBuilder.ToString(), pageIndex);
        }

        public override void SetRuleConfigStatus(int id, int status) {
            this.Execute("Update RuleConfig set Status = :status where Id = :id", new { status, id });
        }

        public override void SetRuleConfigRunStatus(int id, int runStatus) {
            this.Execute("Update RuleConfig set RunStatus = :runStatus where Id = :id", new { runStatus, id });
        }

        /// <summary>
        /// 新增调度配置
        /// </summary>
        /// <param name="config">配置</param>
        /// <returns>保存结果</returns>
        public override MessageInformation InsertRule(RuleConfig config) {
            var messageInformation = new MessageInformation();
            try {
                config.UniqueCode = Utils.Guid;
                bool isValid =
                    Connection.QueryFirstOrDefault<RuleConfig>(
                        "Select * from RuleConfig r where r.jobName = :JobName or triggername = :TriggerName or servicename= :ServiceName",
                        config) == null;
                if(!isValid) {
                    throw new ValidationException("JobName,TriggerName,MethodName必须唯一");
                }
                string sql = @"insert into RuleConfig
                              (id,
                               cron,
                               description,
                               triggername,
                               jobname,
                               method,
                               postbody,
                               servicename,
                               author,
                               contenttype,
                               isauthentication,
                               username,
                               password,
                               groupname,
                               address,
                               status,
                               isWebService,
                               uniqueCode,
                               runStatus)
                            values
                              (s_RuleConfig.Nextval,
                               :Cron,
                               :Description,
                               :TriggerName,
                               :JobName,
                               :Method,
                               :PostBody,
                               :ServiceName,
                               :Author,
                               :ContentType,
                               :IsAuthentication,
                               :UserName,
                               :Password,
                               :GroupName,
                               :Address,
                               :Status,
                               :IsWebService,
                               :UniqueCode,
                               :RunStatus)";
                this.Execute(sql, config);
                messageInformation.ExecuteSuccess("数据插入成功");
            } catch(Exception e) {
                messageInformation.ExecuteError(e);
            }
            return messageInformation;
        }

        /// <summary>
        /// 修改调度配置
        /// </summary>
        /// <param name="config">配置</param>
        /// <returns>保存结果</returns>
        public override MessageInformation SaveChanges(RuleConfig config) {
            var messageInformation = new MessageInformation();
            try {
                config.UniqueCode = Utils.Guid;
                var data = Connection.QueryFirstOrDefault<RuleConfig>(
                        "Select * from RuleConfig r where (r.jobName = :JobName or triggername = :TriggerName or servicename= :ServiceName) and r.Id <> :Id",
                        config);
                if(data != null) {
                    throw new ValidationException("JobName,TriggerName,MethodName必须唯一");
                }
                string sql = @"Update RuleConfig
                               set Cron             = :Cron,
                                   Description      = :Description,
                                   TriggerName      = :TriggerName,
                                   JobName          = :JobName,
                                   Method           = :Method,
                                   PostBody         = :PostBody,
                                   ServiceName      = :ServiceName,
                                   Author           = :Author,
                                   ContentType      = :ContentType,
                                   IsAuthentication = :IsAuthentication,
                                   UserName         = :UserName,
                                   Password         = :Password,
                                   GroupName        = :GroupName,
                                   Address          = :Address,
                                   Status           = :Status,
                                   IsWebService     = :IsWebService,
                                   UniqueCode       = :UniqueCode,
                                   RunStatus        = :RunStatus
                             where Id = :Id ";
                this.Execute(sql, config);
                messageInformation.ExecuteSuccess("数据更新成功");
            } catch(Exception e) {
                messageInformation.ExecuteError(e);
            }
            return messageInformation;
        }

        public override RuleConfig GetRuleConfigById(int id) {
            return Connection.QueryFirst<RuleConfig>("Select * From RuleConfig where Id = :id", new { id });
        }

        private int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int databaseOption = 1) {
            var info = "SQL语句:" + sql + "  \n SQL参数: " + JsonConvert.SerializeObject(param) + " \n";
            var sw = new Stopwatch();
            sw.Start();
            var result = Connection.Execute(sql, param, transaction, commandTimeout, commandType);
            sw.Stop();
            Logger.Info(info + "耗时:" + sw.ElapsedMilliseconds + (sw.ElapsedMilliseconds > 1000 ? "#####" : string.Empty) + "\n");
            return result;
        }

        private PageData<T> ExecuteWithPaging<T>(string sql, int pageIndex, bool isAppendWhere = false, string sortColumn = "", bool sortAsc = true) where T : class {
            var sqlBuilder = new StringBuilder();
            if(isAppendWhere)
                sqlBuilder.AppendLine($"{sql} where 1=1 ");
            else sqlBuilder.Append($"{sql} ");
            sql = $"select count(*) from ({sql})";
            var page = new PageData<T>();
            var log = new StringBuilder();
            if(!Utils.IsProduction) {
                log.AppendLine($"***************{DateTime.Now:执行时间：yyyy-MM-dd HH:mm:ss}*********************");
                log.AppendLine($"执行总数统计SQL:\n{sql}");
            }
            sqlBuilder.AppendFormat("AND ROWNUM <= {0}*{1}", pageIndex, GlobalVar.PAGE_SIZE);
            if(!string.IsNullOrEmpty(sortColumn))
                sqlBuilder.AppendFormat($"order by {sortColumn}" + (sortAsc ? " ASC " : " DESC "));

            var executeSql = $"SELECT * FROM ({sqlBuilder}) TABLE_ALIAS WHERE TABLE_ALIAS.ROWNO  > {GlobalVar.PAGE_SIZE}*({pageIndex}-1)";

            if(!Utils.IsProduction) {
                log.AppendLine("-----------------------");
                log.AppendLine($"执行分页数据查询SQL:\n{executeSql}");
                log.AppendLine($"***************{DateTime.Now:执行结束：yyyy-MM-dd HH:mm:ss}*********************");
                Logger.Info(log.ToString());
            }
            page.Total = Convert.ToInt32(Connection.ExecuteScalar(sql));
            page.Data = Connection.Query<T>(executeSql).ToList();
            return page;
        }
    }
}
